﻿using Archives.IService;
using Archives.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.IO;
using Archives.Repositories;
using System.Text;
using Appkiz.Apps.Workflow.Library;
using Appkiz.Library.UI;
using Archives.Entities;
using System.Net;
using System.Runtime.InteropServices;
using System.Diagnostics;
using Archives.Extensions;
using System.Net.Http;
using ICSharpCode.SharpZipLib.Zip;
using ICSharpCode.SharpZipLib.Checksums;
using System.Threading;
using System.Data;
using Archives.Common;
using Appkiz.Library.Security;
using Appkiz.Library.Security.Authentication;

namespace Archives.Controllers
{
    public class WorkflowQueryController : BaseController
    {
        private readonly IWorkflowQueryService _service;
        private readonly IRepository _repository;

        public WorkflowQueryController(IWorkflowQueryService service, IRepository repository)
        {
            _service = service;
            _repository = repository;
        }

        public ActionResult GetWorkflowQueryInfo(Models.WorkflowQuery query)
        {
            return JsonDate(_service.GetWorkflowQueryInfo(query));
        }

        #region 导出工作流表单
        /// <summary>
        /// 导出工作流表单
        /// </summary>
        /// <param name="mid">工作流Id</param>
        /// <param name="path">路径</param>
        /// <param name="documentName">公文标题</param>
        /// <returns></returns>
        public FilePathResult ExportToPdf(string mid, string path, string documentName)
        {
            FilePathResult result;
            //通过mid获取表单信息
            var sheetInfo = from a in _repository.All<WKF_Doc>(m => m.MessageID == mid)
                            join b in _repository.All<WKF_Worksheet>()
                            on a.DocHelperID equals b.WorksheetID
                            select b;
            //表单Id
            var table = sheetInfo.FirstOrDefault().Document;

            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);

            var sheetId = sheetInfo.FirstOrDefault().WorksheetID;

            var smgr = new SheetMgr();
            //表单信息
            var worksheet = smgr.GetWorksheet(sheetId);

            //表单的路径
            var tablePath = Path.Combine(path, documentName + ".pdf");

            worksheet.ExportFile(Worksheet.ExportFileType.PDF, tablePath);
            result = this.File(tablePath, "application/pdf", documentName + ".pdf");
            DownloadAtt(sheetId, string.Format(@"{0}", path));
            return result;
        }
        #endregion

        #region 附件下载
        /// <summary>
        /// 附件下载
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ActionResult DownloadAtt(string sheetId, string path)
        {
            var smgr = new SheetMgr();
            Worksheet sheet = smgr.GetWorksheet(sheetId);

            int max_col, max_row;
            var cellList = sheet.FindWorkcells(out max_row, out max_col); //得到所有单元格
                                                                          //foreach (var cell in cellList)
                                                                          //{
                                                                          //    if (cell.WorkcellDataSource == Enum_WorkcellDataSource.Attachment)
                                                                          //    {
                                                                          //        //附件Ids
                                                                          //        var attId = cell.WorkcellInternalValue;
                                                                          //        if (!string.IsNullOrWhiteSpace(attId))
                                                                          //        {
                                                                          //            var attIds = attId.Split(';');
                                                                          //            if (attIds != null)
                                                                          //            {
                                                                          //                for (int i = 0; i < attIds.Count(); i++)
                                                                          //                {
                                                                          //                    var filePa = string.Empty;
                                                                          //                    var id = attIds[i];
                                                                          //                    var fileInfo = _repository.FirstOrDefault<WKF_WorkflowAttachment>(m => m.AttID == id);
                                                                          //
                                                                          //                    WebPage webPage = new WebPage(base.HttpContext.ApplicationInstance.Context);
                                                                          //                    string attAddress = webPage.AppDataDir("Att") + id;
                                                                          //                    attAddress = attAddress.Replace("Archives", "workflow");
                                                                          //                    var wmgr = new WorkflowMgr();
                                                                          //                    Attachment attachment = wmgr.GetAttachment(id);
                                                                          //                    filePa = attAddress;
                                                                          //                    if (attachment != null)
                                                                          //                    {
                                                                          //                        //如果文件的ceb文件
                                                                          //                        if (fileInfo != null && fileInfo.AttExtension.ToLower() == "ceb")
                                                                          //                        {
                                                                          //                            //先在服务器上转换成pdf
                                                                          //                            CebToPdf(attAddress);
                                                                          //                        }
                                                                          //                    }
                                                                          //                }
                                                                          //            }
                                                                          //        }
                                                                          //    }
                                                                          //}

            foreach (var cell in cellList)
            {
                if (cell.WorkcellDataSource == Enum_WorkcellDataSource.Attachment)
                {
                    //附件Ids
                    var attId = cell.WorkcellInternalValue;
                    if (!string.IsNullOrWhiteSpace(attId))
                    {
                        var attIds = attId.Split(';');
                        if (attIds != null)
                        {
                            for (int i = 0; i < attIds.Count(); i++)
                            {
                                var filePa = string.Empty;
                                var id = attIds[i];
                                var fileInfo = _repository.FirstOrDefault<WKF_WorkflowAttachment>(m => m.AttID == id);

                                WebPage webPage = new WebPage(base.HttpContext.ApplicationInstance.Context);
                                string attAddress = webPage.AppDataDir("Att") + id;
                                attAddress = attAddress.Replace("Archives", "workflow");
                                byte[] byteFile = null;
                                var wmgr = new WorkflowMgr();
                                Attachment attachment = wmgr.GetAttachment(id);
                                filePa = attAddress;
                                if (attachment != null)
                                {
                                    //如果文件的ceb文件
                                    if (fileInfo != null && fileInfo.AttExtension.ToLower() == "ceb")
                                    {
                                        //先在服务器上转换成pdf
                                        //CebToPdf(attAddress);
                                        //获取pdf文件
                                        var counts = id.Count();
                                        filePa = webPage.AppDataDir("Att").Replace("Archives", "workflow") + id.Substring(0, counts - 4) + ".pdf";
                                        //如果的ceb文件把输出格式改成pdf
                                        attachment.AttFileName = attachment.AttFileName.Replace(attachment.AttExtension, "pdf");
                                    }
                                    //获取文件流
                                    var fsRead = new FileStream(filePa, FileMode.Open, FileAccess.Read, FileShare.Read);
                                    if (fsRead.Length == 0)
                                    {
                                        byteFile = new byte[1];
                                    }
                                    else
                                    {
                                        byteFile = new byte[fsRead.Length];
                                    }
                                    var count = fsRead.Read(byteFile, 0, byteFile.Length);
                                    fsRead.Close();
                                    var filePath = string.Format(@"{0}\{1}", path, attachment.AttFileName);
                                    //创建文件
                                    FileStream fs = new FileStream(filePath, FileMode.Append, FileAccess.Write);
                                    fs.Write(byteFile, 0, count);
                                    fs.Close();  //关闭数据流
                                                 //如果文件的ceb文件，删除转换后的pdf文件
                                    if (fileInfo.AttExtension.ToLower() == "ceb")
                                    {
                                        System.IO.File.Delete(filePa);
                                    }
                                }
                            }
                        }
                    }
                }
            }
            return null;
        }
        #endregion

        #region 检索后信息
        /// <summary>
        /// 检索后信息
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        private IQueryable<WorkflowModel> GetWorkflowModel(Models.WorkflowQuery query)
        {
            var data = from a in _repository.All<GW_ReceiveFile>(m => m.IsFirstLoading.HasValue && !m.IsFirstLoading.Value)
                       join b in _repository.All<WKF_Message>(m => m.MessageStatus != 3)
                       on a.MessageId equals b.MessageID
                       join c in _repository.All<ORG_Employee>()
                       on b.MessageIssuedBy equals c.EmplID
                       join d in _repository.All<ORG_Department>()
                       on b.MessageIssuedDept equals d.DeptID
                       select new WorkflowModel
                       {
                           MessageId = a.MessageId,
                           WorkFlowId = a.WorkFlowId,
                           ClosedOrHairTime = a.ClosedOrHairTime,
                           DocumentNumber = a.DocumentNumber,
                           DocumentTitle = a.DocumentTitle,
                           SequenceName = a.SequenceName,
                           SequenceNumber = a.SequenceNumber,
                           MessageIssuedBy = b.MessageIssuedBy,
                           MessageIssuedName = c != null ? c.EmplName : null,
                           MessageIssuedDept = b.MessageIssuedDept,
                           MessageIssuedDeptName = d != null ? d.DeptName : null,
                           MessageTitle = b.MessageTitle,
                           MessageStatus = b.MessageStatus,
                           MessageCreateTime = b.MessageCreateTime,
                           MessageStatusName = b.MessageStatus == 0 ? "草稿" :
                                                    (b.MessageStatus == 1 ? "运行中" :
                                                    (b.MessageStatus == 2 ? "已完成" :
                                                    (b.MessageStatus == 3 ? "终止信息" : null)))
                       };
            var draft = from a in _repository.All<GW_ReceiveFile>()
                        join b in _repository.All<WKF_Message>(m => m.MessageType == "file" && m.MessageStatus != 3)
                        on a.MessageId equals b.MessageID
                        join c in _repository.All<ORG_Employee>()
                        on b.MessageIssuedBy equals c.EmplID
                        join d in _repository.All<ORG_Department>()
                        on b.MessageIssuedDept equals d.DeptID
                        select new WorkflowModel
                        {
                            MessageId = a.MessageId,
                            WorkFlowId = a.WorkFlowId,
                            ClosedOrHairTime = a.ClosedOrHairTime,
                            DocumentNumber = a.DocumentNumber,
                            DocumentTitle = a.DocumentTitle,
                            SequenceName = a.SequenceName,
                            SequenceNumber = a.SequenceNumber,
                            MessageIssuedBy = b.MessageIssuedBy,
                            MessageIssuedName = c != null ? c.EmplName : null,
                            MessageIssuedDept = b.MessageIssuedDept,
                            MessageIssuedDeptName = d != null ? d.DeptName : null,
                            MessageTitle = b.MessageTitle,
                            MessageStatus = b.MessageStatus,
                            MessageCreateTime = b.MessageCreateTime,
                            MessageStatusName = b.MessageStatus == 0 ? "草稿" :
                                                     (b.MessageStatus == 1 ? "运行中" :
                                                     (b.MessageStatus == 2 ? "已完成" :
                                                     (b.MessageStatus == 3 ? "终止信息" : null)))
                        };
            data = data.Union(draft).Distinct();
            if (query.PageNumber <= 0)
            {
                query.PageNumber = 1;
            }
            if (query.PageSize <= 0)
            {
                query.PageSize = 20;
            }
            //流程类型
            if (!string.IsNullOrWhiteSpace(query.WorkFlowId))
            {
                data = data.Where(m => m.WorkFlowId == query.WorkFlowId);
            }
            //流程状态
            if (query.MessageStatus.HasValue)
            {
                data = data.Where(m => m.MessageStatus == query.MessageStatus.Value);
            }
            //开始收发文时间
            if (query.StartClosedOrHairTime.HasValue)
            {
                data = data.Where(m => m.ClosedOrHairTime >= query.StartClosedOrHairTime.Value);
            }
            //结束收发文时间
            if (query.EndClosedOrHairTime.HasValue)
            {
                data = data.Where(m => m.ClosedOrHairTime <= query.EndClosedOrHairTime.Value);
            }
            //来文号
            if (!string.IsNullOrWhiteSpace(query.DocumentNumber))
            {
                data = data.Where(m => m.DocumentNumber.Contains(query.DocumentNumber));
            }
            //收文序列
            if (!string.IsNullOrWhiteSpace(query.SequenceName))
            {
                data = data.Where(m => m.SequenceName == query.SequenceName);
            }
            //收文号
            if (!string.IsNullOrWhiteSpace(query.SequenceNumber))
            {
                data = data.Where(m => m.SequenceNumber.Contains(query.SequenceNumber));
            }
            //发起部门
            if (!string.IsNullOrWhiteSpace(query.MessageIssuedDept))
            {
                data = data.Where(m => m.MessageIssuedDept == query.MessageIssuedDept);
            }
            //发起人
            if (!string.IsNullOrWhiteSpace(query.MessageIssuedBy))
            {
                data = data.Where(m => m.MessageIssuedBy == query.MessageIssuedBy);
            }
            //模糊检索
            if (!string.IsNullOrWhiteSpace(query.QueryCondition))
            {
                data = data.Where(m => m.DocumentTitle.Contains(query.QueryCondition));
            }
            return data;
        }
        #endregion

        #region ceb转pdf
        /// <summary>
        /// ceb转pdf
        /// </summary>
        /// <param name="path"></param>
        public void CebToPdf(string path)
        {
            Process exep = new Process();
            exep.StartInfo.FileName = Path.Combine(Server.MapPath("/") + "bin/", "CEB2PDF.exe"); //string.Format(@"D:\TFS\OA_Workflow\Archives\bin\CEB2PDF.exe");
            exep.StartInfo.UseShellExecute = false;
            exep.StartInfo.RedirectStandardError = true;//开启出错返回信息
            exep.StartInfo.RedirectStandardOutput = true;//开房输出返回信息
            exep.StartInfo.RedirectStandardInput = true;
            exep.StartInfo.CreateNoWindow = false;
            exep.StartInfo.Arguments = path;
            exep.Start();
            //string output = exep.StandardOutput.ReadToEnd();
            exep.Close();
        }
        #endregion

        #region 导出工作流信息
        /// <summary>
        /// 导出工作流信息
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageNumber"></param>
        /// <param name="queryCondition"></param>
        /// <param name="messageStatus"></param>
        /// <param name="startClosedOrHairTime"></param>
        /// <param name="endClosedOrHairTime"></param>
        /// <param name="documentNumber"></param>
        /// <param name="sequenceNumber"></param>
        /// <param name="sequenceName"></param>
        /// <param name="messageIssuedBy"></param>
        /// <param name="messageIssuedDept"></param>
        /// <param name="workFlowId"></param>
        /// <returns></returns>
        [HttpGet]
        public ActionResult DownLoadWorkflow(int pageSize, int pageNumber, string queryCondition, int? messageStatus, DateTime? startClosedOrHairTime,
            DateTime? endClosedOrHairTime, string documentNumber, string sequenceNumber, string sequenceName, string messageIssuedBy,
            string messageIssuedDept, string workFlowId)
        {
            var query = new Models.WorkflowQuery
            {
                PageNumber = pageNumber,
                PageSize = pageSize,
                QueryCondition = queryCondition,
                MessageIssuedBy = messageIssuedBy,
                MessageIssuedDept = messageIssuedDept,
                MessageStatus = messageStatus,
                DocumentNumber = documentNumber,
                WorkFlowId = workFlowId,
                EndClosedOrHairTime = endClosedOrHairTime,
                SequenceName = sequenceName,
                SequenceNumber = sequenceNumber,
                StartClosedOrHairTime = startClosedOrHairTime
            };

            CompressFileZip fileZip = new CompressFileZip();
            //获取虚拟目录
            var path = Server.MapPath("/") + @"AttachmentFiles";
            var data = GetWorkflowModel(query).ToList();
            //路径集合
            var pathList = new List<string>();
            var docPath = string.Empty;
            foreach (var item in data)
            {
                string finalDocTitle = Path.GetInvalidFileNameChars().Aggregate(item.DocumentTitle, (current, c) => current.Replace(c.ToString(), "."));
                docPath = fileZip.CreateTempFolder(finalDocTitle, path, item.MessageTitle);
                ExportToPdf(item.MessageId, docPath, "公文处理单");
                pathList.Add(docPath);
            }
            //压缩文件
            ZipHelper.CreateZip(path, path + ".zip");
            DelectDir(path);

            var name = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString()
                        + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();

            return File(path + ".zip", "application/zip", name + ".zip");
        }
        #endregion

        #region 导出前先把ceb转pdf
        /// <summary>
        /// 导出前先把ceb转pdf
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageNumber"></param>
        /// <param name="queryCondition"></param>
        /// <param name="messageStatus"></param>
        /// <param name="startClosedOrHairTime"></param>
        /// <param name="endClosedOrHairTime"></param>
        /// <param name="documentNumber"></param>
        /// <param name="sequenceNumber"></param>
        /// <param name="sequenceName"></param>
        /// <param name="messageIssuedBy"></param>
        /// <param name="messageIssuedDept"></param>
        /// <param name="workFlowId"></param>
        [HttpGet]
        public bool CebGeneratePdf(int pageSize, int pageNumber, string queryCondition, int? messageStatus, DateTime? startClosedOrHairTime,
            DateTime? endClosedOrHairTime, string documentNumber, string sequenceNumber, string sequenceName, string messageIssuedBy,
            string messageIssuedDept, string workFlowId)
        {
            try
            {
                var query = new Models.WorkflowQuery
                {
                    PageNumber = pageNumber,
                    PageSize = pageSize,
                    QueryCondition = queryCondition,
                    MessageIssuedBy = messageIssuedBy,
                    MessageIssuedDept = messageIssuedDept,
                    MessageStatus = messageStatus,
                    DocumentNumber = documentNumber,
                    WorkFlowId = workFlowId,
                    EndClosedOrHairTime = endClosedOrHairTime,
                    SequenceName = sequenceName,
                    SequenceNumber = sequenceNumber,
                    StartClosedOrHairTime = startClosedOrHairTime
                };
                var data = GetWorkflowModel(query).ToList();
                foreach (var item in data)
                {
                    var sheetInfo = from a in _repository.All<WKF_Doc>(m => m.MessageID == item.MessageId)
                                    join b in _repository.All<WKF_Worksheet>()
                                    on a.DocHelperID equals b.WorksheetID
                                    select b;
                    var sheetId = sheetInfo.FirstOrDefault().WorksheetID;
                    var smgr = new SheetMgr();
                    Worksheet sheet = smgr.GetWorksheet(sheetId);

                    int max_col, max_row;
                    var cellList = sheet.FindWorkcells(out max_row, out max_col); //得到所有单元格
                    foreach (var cell in cellList)
                    {
                        if (cell.WorkcellDataSource == Enum_WorkcellDataSource.Attachment)
                        {
                            //附件Ids
                            var attId = cell.WorkcellInternalValue;
                            if (!string.IsNullOrWhiteSpace(attId))
                            {
                                var attIds = attId.Split(';');
                                if (attIds != null)
                                {
                                    for (int i = 0; i < attIds.Count(); i++)
                                    {
                                        var filePa = string.Empty;
                                        var id = attIds[i];
                                        var fileInfo = _repository.FirstOrDefault<WKF_WorkflowAttachment>(m => m.AttID == id);

                                        WebPage webPage = new WebPage(base.HttpContext.ApplicationInstance.Context);
                                        string attAddress = webPage.AppDataDir("Att") + id;
                                        attAddress = attAddress.Replace("Archives", "workflow");
                                        var wmgr = new WorkflowMgr();
                                        Attachment attachment = wmgr.GetAttachment(id);
                                        filePa = attAddress;
                                        if (attachment != null)
                                        {
                                            //如果文件的ceb文件
                                            if (fileInfo != null && fileInfo.AttExtension.ToLower() == "ceb")
                                            {
                                                //先在服务器上转换成pdf
                                                CebToPdf(attAddress);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                Thread.Sleep(1000);
                return true;
            }
            catch (Exception)
            {

                return false;
            }
        }
        #endregion

        #region 删除指定路径下文件
        /// <summary>
        /// 删除指定路径下文件
        /// </summary>
        /// <param name="srcPath"></param>
        public static void DelectDir(string srcPath)
        {
            try
            {
                DirectoryInfo dir = new DirectoryInfo(srcPath);
                FileSystemInfo[] fileinfo = dir.GetFileSystemInfos();  //返回目录中所有文件和子目录
                foreach (FileSystemInfo i in fileinfo)
                {
                    if (i is DirectoryInfo)            //判断是否文件夹
                    {
                        DirectoryInfo subdir = new DirectoryInfo(i.FullName);
                        subdir.Delete(true);          //删除子目录和文件
                    }
                    else
                    {
                        System.IO.File.Delete(i.FullName);      //删除指定文件
                    }
                }
            }
            catch (Exception e)
            {
                throw;
            }
        }
        #endregion

        #region 删除zip压缩包
        /// <summary>
        /// 删除zip压缩包
        /// </summary>
        [HttpGet]
        public void DeleteZip()
        {
            //获取虚拟目录
            var path = Server.MapPath("/") + @"AttachmentFiles.zip";
            System.IO.File.Delete(path);
        }
        #endregion

        #region 获取人员所在角色
        /// <summary>
        /// 获取人员所在角色
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public ActionResult GetRoleInfo()
        {
            //当前人
            var emplId = (User.Identity as Appkiz.Library.Security.Authentication.AppkizIdentity).Employee.EmplID;
            var data = from a in _repository.All<ORG_Employee>(m => m.EmplID == emplId)
                       join b in _repository.All<ORG_EmplRole>()
                       on a.EmplID equals b.EmplID
                       join c in _repository.All<ORG_Role>()
                       on b.RoleID equals c.RoleID
                       select new
                       {
                           RoleID = c.RoleID,
                           RoleName = c.RoleName
                       };
            return Json(data);
        }
        #endregion

        #region 获取流程下拉列表
        /// <summary>
        /// 获取流程下拉列表
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public ActionResult GetTempList()
        {
            var data = _repository.All<WKF_Message>(m => m.IsTemplate.HasValue && m.IsTemplate.Value == 1)
                       .Select(m => new
                       {
                           ID = m.MessageID,
                           Title = m.MessageTypeKey
                       }).Distinct();
            return Json(data);
        }
        #endregion

        #region 审批监测列表
        [HttpPost]
        public object GetDeptMonitor(Models.WorkflowQuery query)
        {
            try
            {
                /*
                 * 变量定义
                 */


                /*
                 * 参数获取
                 */
                // 来文文号
                var DocumentNumber = query.DocumentNumber;
                // 公文标题
                var DocumentTitle = query.QueryCondition;

                if (query.PageNumber <= 0)
                {
                    query.PageNumber = 1;
                }
                if (query.PageSize <= 0)
                {
                    query.PageSize = 20;
                }
                /*
                 * 获取列表
                 */
                // 搜索条件
                var sql = @"SELECT 
                          DISTINCT(Handle.MessageID),
                          ReceiveFile.DocumentNumber,
                          ReceiveFile.DocumentTitle,
                          STUFF((
                            SELECT ', ' + EmplName
                            FROM WKF_MessageHandle
                                JOIN
                                    ORG_Employee
                                ON 
                                    WKF_MessageHandle.UserID = ORG_Employee.EmplID
                                AND
                                    NodeName = '部门承办'
                                AND
                                    WKF_MessageHandle.MessageID = Handle.MessageID
                                AND 
                                    DATEDIFF(HOUR, WKF_MessageHandle.CreateTime, GETDATE()) >= 24
                            FOR XML PATH(''), TYPE).value('(./text())[1]', 'VARCHAR(MAX)')
                          ,1,2,'') AS Users,
                          STUFF((
                            SELECT
                                ',' + CONVERT(VARCHAR, WKF_MessageHandle.CreateTime, 20)
                            FROM
                                WKF_MessageHandle
                            WHERE(
                                WKF_MessageHandle.MessageID = Handle.MessageID 
                            AND 
                                NodeName = '部门承办'
                            AND
                                DATEDIFF(HOUR, WKF_MessageHandle.CreateTime, GETDATE()) >= 24)
                            FOR XML PATH(''))
                          ,1,1,'') AS CreateTime,
                          STUFF((
                            SELECT
                                ',' + CONVERT(VARCHAR, DATEDIFF(HOUR, CreateTime, GETDATE()))
                            FROM
                                WKF_MessageHandle
                            WHERE(
                                WKF_MessageHandle.MessageID = Handle.MessageID
                            AND
                                NodeName = '部门承办'
                            AND
                                DATEDIFF(HOUR, WKF_MessageHandle.CreateTime, GETDATE()) >= 24)
                            FOR XML PATH(''))
                          ,1,1,'') AS DelayTime
                        FROM
                            WKF_MessageHandle Handle
                        INNER JOIN
                            GW_ReceiveFile ReceiveFile
                        ON
                            NodeName = '部门承办'
                        AND
                            Handle.MessageID = ReceiveFile.MessageID
                        AND
                            DATEDIFF(HOUR, CreateTime, GETDATE()) >= 24
                        ";
                // 来文文号
                if (!string.IsNullOrEmpty(DocumentNumber))
                {
                    sql += string.Format(@" AND DocumentNumber LIKE '%{0}%'", DocumentNumber);
                }
                // 公文标题
                if(!string.IsNullOrEmpty(DocumentTitle))
                {
                    sql += string.Format(@" AND DocumentTitle LIKE '%{0}%'", DocumentTitle);
                }
                var paginationSql = sql + @" ORDER BY DelayTime DESC OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY";
                List<object> resultList = DbUtil.ExcuteSqlCommand(sql, DbUtil.DeptMonitorHandle).ToList();
                List<object> pageResult = DbUtil.ExcuteSqlCommand(string.Format(paginationSql, (query.PageNumber - 1) * query.PageSize, query.PageSize), DbUtil.DeptMonitorHandle).ToList();
                //总数
                var totalRecords = resultList.Count();
                //总页数
                var totalPages = totalRecords % query.PageSize == 0 ? totalRecords / query.PageSize : totalRecords / query.PageSize + 1;

                return Json(new
                {
                    Data = pageResult,
                    TotalInfo = new
                    {
                        TotalRecords = totalRecords,
                        TotalPages = totalPages
                    }
                }, JsonRequestBehavior.AllowGet);
            }
            catch (Exception e)
            {
                return Json(new
                {
                    Succeed = false,
                    Message = e.Message
                }, JsonRequestBehavior.AllowGet);
            }
        }
        #endregion

        #region 某部门审批监测列表
        public object GetTheDeptMonitor(FormCollection collection)
        {
            try
            {
                /*
                 * 获取参数
                 */
                // 部门ID
                var deptID = collection["deptID"];
                // 超时时常
                var delayTime = collection["delayTime"];
                // 开始日期
                var startTime = collection["startTime"];
                // 结束日期
                var endTime = collection["endTime"];

                /*
                 * 获取列表
                 */
                // 搜索条件
                var sql = @"SELECT 
                              distinct(Handle.MessageID),
                              ReceiveFile.DocumentNumber,
                              ReceiveFile.DocumentTitle,
                              STUFF((
                                SELECT ',' + EmplName
                                FROM TGR_HandleRecord, ORG_Department, ORG_Employee
                                where (TGR_HandleRecord.UserID = ORG_Employee.EmplID AND TGR_HandleRecord.MessageID = Handle.MessageID AND NodeName = '部门承办' AND DATEDIFF(HOUR, TGR_HandleRecord.CreateTime, CASE WHEN TGR_HandleRecord.HandleTime IS NULL THEN GETDATE() ELSE TGR_HandleRecord.HandleTime END) >= {0} AND  ORG_Department.DeptID = CASE WHEN Handle.HandleTime IS NULL THEN  (SELECT DeptID FROM ORG_Employee WHERE EmplID = UserID) ELSE HandlerDeptID END AND ORG_Department.DeptID = '{1}') 
                                FOR XML PATH(''))
                              ,1,1,'') AS Users,
                              STUFF((
                                SELECT ',' + CONVERT(VARCHAR, TGR_HandleRecord.CreateTime, 20)
                                FROM TGR_HandleRecord, ORG_Department
                                where (TGR_HandleRecord.MessageID = Handle.MessageID AND NodeName = '部门承办' AND DATEDIFF(HOUR, TGR_HandleRecord.CreateTime, CASE WHEN TGR_HandleRecord.HandleTime IS NULL THEN GETDATE() ELSE TGR_HandleRecord.HandleTime END) >= {0} AND  ORG_Department.DeptID = CASE WHEN Handle.HandleTime IS NULL THEN  (SELECT DeptID FROM ORG_Employee WHERE EmplID = UserID) ELSE HandlerDeptID END AND ORG_Department.DeptID = '{1}') 
                                FOR XML PATH(''))
                              ,1,1,'') AS CreateTime,
                              STUFF((
                                SELECT ',' + CONVERT(VARCHAR, DATEDIFF(HOUR, TGR_HandleRecord.CreateTime, CASE WHEN TGR_HandleRecord.HandleTime IS NULL THEN GETDATE() ELSE TGR_HandleRecord.HandleTime END))
                                FROM TGR_HandleRecord, ORG_Department
                                where (TGR_HandleRecord.MessageID = Handle.MessageID AND NodeName = '部门承办' AND DATEDIFF(HOUR, TGR_HandleRecord.CreateTime, CASE WHEN TGR_HandleRecord.HandleTime IS NULL THEN GETDATE() ELSE TGR_HandleRecord.HandleTime END) >= {0} AND ORG_Department.DeptID = CASE WHEN Handle.HandleTime IS NULL THEN  (SELECT DeptID FROM ORG_Employee WHERE EmplID = UserID) ELSE HandlerDeptID END AND ORG_Department.DeptID = '{1}') 
                                FOR XML PATH(''))
                              ,1,1,'') AS DelayTime
                            FROM 
	                            TGR_HandleRecord Handle
                            INNER JOIN 
	                            GW_ReceiveFile ReceiveFile
                            ON
	                            NodeName = '部门承办'
                            AND
	                            Handle.MessageID = ReceiveFile.MessageID
                            AND
	                            DATEDIFF(HOUR, Handle.CreateTime, CASE WHEN Handle.HandleTime IS NULL THEN GETDATE() ELSE Handle.HandleTime END) >= {0}
                            INNER JOIN
	                            ORG_Department Dept
                            ON
	                            Dept.DeptID =  CASE WHEN Handle.HandleTime IS NULL THEN  (SELECT DeptID FROM ORG_Employee WHERE EmplID = UserID) ELSE HandlerDeptID END
                            AND
	                            Dept.DeptID = '{1}'";
                sql = string.Format(sql, delayTime, deptID);
                // 开始日期
                if(!string.IsNullOrEmpty(startTime))
                {
                    sql += string.Format(@" AND Handle.CreateTime >= '{0}'", startTime);
                }
                // 结束日期
                if(!string.IsNullOrEmpty(endTime))
                {
                    sql += string.Format(@" AND Handle.CreateTime <= '{0}'", endTime);
                }
                List<object> resultList = DbUtil.ExcuteSqlCommand(sql, DbUtil.TheDeptMonitorHandle).ToList();
                return Json(new
                {
                    Data = resultList
                }, JsonRequestBehavior.AllowGet);
            }
            catch (Exception e)
            {
                return Json(new
                {
                    Succeed = false,
                    Message = e.Message
                }, JsonRequestBehavior.AllowGet);
            }
        }
        #endregion

        #region 领导审批通知列表
        [HttpPost]
        public object GetLeaderNotify(Models.WorkflowQuery query, int Checked)
        {
            try
            {
                /*
                 * 参数获取
                 */
                // 来文文号
                var DocumentNumber = query.DocumentNumber;
                // 公文标题
                var DocumentTitle = query.QueryCondition;
                // 当前用户
                var employee = (User.Identity as AppkizIdentity).Employee;

                if (query.PageNumber <= 0)
                {
                    query.PageNumber = 1;
                }
                if (query.PageSize <= 0)
                {
                    query.PageSize = 20;
                }
                /*
                 * 获取列表
                 */
                // 搜索条件
                var sql = @"SELECT 
                                DISTINCT(a.MessageID),
                                ReceiveFile.DocumentNumber,
                                ReceiveFile.DocumentTitle,
                                STUFF((
                                    SELECT ', ' + EmplName
                                FROM TGR_LeaderOpinion
                                    JOIN
                                        ORG_Employee
                                    ON 
                                        TGR_LeaderOpinion.Sender = ORG_Employee.EmplID
   	                                AND TGR_LeaderOpinion.Checked = {0}
                                    AND TGR_LeaderOpinion.Receiver = {1}
                                    AND TGR_LeaderOpinion.MessageID = a.MessageID
                                FOR XML PATH(''), TYPE).value('(./text())[1]', 'VARCHAR(MAX)')
                                ,1,2,'') AS Users,
                                STUFF((
                                SELECT
                                    ',' + TGR_LeaderOpinion.Opinion
                                FROM
                                    TGR_LeaderOpinion
                                WHERE(TGR_LeaderOpinion.Checked = {0} AND TGR_LeaderOpinion.Receiver = {1} AND TGR_LeaderOpinion.MessageID = a.MessageID)
                                FOR XML PATH(''))
                                ,1,1,'') AS Opinion,
                                STUFF((
                                SELECT
                                    ',' + CONVERT(VARCHAR, CreateTime, 20)
                                FROM
                                    TGR_LeaderOpinion
                                WHERE(TGR_LeaderOpinion.Checked = {0} AND TGR_LeaderOpinion.Receiver = {1} AND TGR_LeaderOpinion.MessageID = a.MessageID)
                                FOR XML PATH('')),1,1,'') AS CreateTime
                                FROM
                                    TGR_LeaderOpinion a
                                INNER JOIN
                                    GW_ReceiveFile ReceiveFile
                                ON
                                    a.MessageID = ReceiveFile.MessageID
                                AND
                                    a.Checked = {0}
                                AND
                                    a.Receiver = {1}";
                sql = string.Format(sql, Checked, employee.EmplID);

                // 来文文号
                if (!string.IsNullOrEmpty(DocumentNumber))
                {
                    sql += string.Format(@" AND DocumentNumber LIKE '%{0}%'", DocumentNumber);
                }
                // 公文标题
                if (!string.IsNullOrEmpty(DocumentTitle))
                {
                    sql += string.Format(@" AND DocumentTitle LIKE '%{0}%'", DocumentTitle);
                }
                var paginationSql = sql + @" ORDER BY CreateTime DESC OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY";
                List<object> resultList = DbUtil.ExcuteSqlCommand(sql, DbUtil.LeaderNotifyHandle).ToList();
                List<object> pageResult = DbUtil.ExcuteSqlCommand(string.Format(paginationSql, (query.PageNumber - 1) * query.PageSize, query.PageSize), DbUtil.LeaderNotifyHandle).ToList();
                //总数
                var totalRecords = resultList.Count();
                //总页数
                var totalPages = totalRecords % query.PageSize == 0 ? totalRecords / query.PageSize : totalRecords / query.PageSize + 1;

                return Json(new
                {
                    Data = pageResult,
                    TotalInfo = new
                    {
                        TotalRecords = totalRecords,
                        TotalPages = totalPages
                    }
                }, JsonRequestBehavior.AllowGet);
            }
            catch (Exception e)
            {
                return Json(new
                {
                    Succeed = false,
                    Message = e.Message
                }, JsonRequestBehavior.AllowGet);
            }
        }
        #endregion

        #region 领导审批通知列表（待办）
        [HttpPost]
        public object GetTodoLeaderNotify(Models.WorkflowQuery query)
        {
            return GetLeaderNotify(query, 0);
        }
        #endregion

        #region 领导审批通知列表（已办）
        [HttpPost]
        public object GetDoneLeaderNotify(Models.WorkflowQuery query)
        {
            return GetLeaderNotify(query, 1);
        }
        #endregion

        #region 审批监测统计
        [HttpPost]
        public ActionResult DeptStatstics(DeptStatisticsQueryInfo query)
        {
            try
            {
                /*
                 * 参数校验
                 */
                // 分页页数
                if(query.PageNumber < 1)
                {
                    query.PageNumber = 1;
                }
                // 分页大熊啊
                if(query.PageSize < 0)
                {
                    query.PageSize = 20;
                }
                
                /*
                 * 构建查询语句
                 */
                var sql = @"SELECT
	                            Count(ID) as Times, b.DeptName, a.DeptID, a.DeptName
                            FROM
	                            TGR_HandleRecord
                            INNER JOIN
	                            ORG_Department a
                            ON
	                            a.DeptID =  CASE WHEN TGR_HandleRecord.HandleTime IS NULL THEN  (SELECT DeptID FROM ORG_Employee WHERE EmplID = UserID) ELSE HandlerDeptID END
                            INNER JOIN
	                            ORG_Department b
                            ON
	                            b.DeptHierarchyCode = (SELECT SUBSTRING(c.DeptHierarchyCode, 1, 5) FROM ORG_Department c WHERE c.DeptID = CASE WHEN TGR_HandleRecord.HandleTime IS NULL THEN  (SELECT DeptID FROM ORG_Employee WHERE EmplID = UserID) ELSE HandlerDeptID END)
                            AND
	                            TGR_HandleRecord.NodeName = '部门承办'
                        ";
                // 开始日期
                if (query.StartTime != null)
                {
                    sql += string.Format(@" AND TGR_HandleRecord.CreateTime >= '{0}'", query.StartTime.Value);
                }
                // 结束日期
                if (query.EndTime != null)
                {
                    sql += string.Format(@" AND TGR_HandleRecord.CreateTime <= '{0}'", query.EndTime.Value);
                }
                // 分组信息
                sql += string.Format(@" AND
	                    DATEDIFF(HOUR, TGR_HandleRecord.CreateTime, CASE WHEN TGR_HandleRecord.HandleTime IS NULL THEN GETDATE() ELSE TGR_HandleRecord.HandleTime END) >= {0}
                    GROUP BY
	                    b.DeptName, a.DeptID, a.DeptName
                    ORDER BY
	                    Times
                    DESC", query.DelayTime);
                // 分页sql
                var paginationSql = sql + string.Format(@" OFFSET {0} ROWS FETCH NEXT {1} ROWS ONLY", (query.PageNumber - 1) * query.PageSize, query.PageSize);
                // 查询数据库
                var resultList = DbUtil.ExcuteSqlCommand(sql, DbUtil.DeptStatsticsHandle);
                var pageResult = DbUtil.ExcuteSqlCommand(paginationSql, DbUtil.DeptStatsticsHandle);
                //总数
                var totalRecords = resultList.Count();
                //总页数
                var totalPages = totalRecords % query.PageSize == 0 ? totalRecords / query.PageSize : totalRecords / query.PageSize + 1;

                return Json(new
                {
                    Data = pageResult,
                    TotalInfo = new
                    {
                        TotalRecouds = totalRecords,
                        TotalPages = totalPages
                    }
                }, JsonRequestBehavior.AllowGet);
            }
            catch(Exception e)
            {
                return Json(new
                {
                    Succeed = false,
                    Message = e.Message
                }, JsonRequestBehavior.AllowGet);
            }
        }
        #endregion
    }
}

